package com.lin.sms.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.lin.sms.dao.IScoreDAOByPage;
import com.lin.sms.dao.util.ScoreRowMapper;
import com.lin.sms.domain.Score;
import com.lin.sms.page.PageResult;
//继承JdbcDaoSupport,可以调用父类方法JdbcTemplate
@Repository(value="scoreDaoByPage")
public class ScoreDAOByPageImpl extends JdbcDaoSupport implements IScoreDAOByPage {

	//保存一条成绩
	@Override
	public void save(Score score) {
		super.getJdbcTemplate().update("INSERT INTO t_score (name,classId,chineseScore,mathScore,englishScore) VALUES (?,?,?,?,?)", 
				score.getName(),score.getClassId(),score.getChineseScore(),score.getMathScore(),score.getEnglishScore());
	}

	//更新某位同学成绩
	@Override
	public void update(Score score) {
		super.getJdbcTemplate().update("UPDATE t_score SET name=?,classId=?,chineseScore=?,mathScore=?,englishScore=? WHERE id=?", 
				score.getName(),score.getClassId(),score.getChineseScore(),score.getMathScore(),score.getEnglishScore(),score.getId());
	}

	//查询某位同学成绩
	@Override
	public List<Score> get(String name, String classId) {
		String sql;
		if (classId.equals("*")||classId.equals("1006")) {
			sql = "SELECT * FROM t_score WHERE name=?";
			List<Score> scores = super.getJdbcTemplate().query(sql,ScoreRowMapper.getRowMapper(),name);
			return scores.size()==0 ? null : scores;
		} else {
			sql = "SELECT * FROM t_score WHERE name=? AND classId=?";
		}
		List<Score> scores = super.getJdbcTemplate().query(sql,ScoreRowMapper.getRowMapper(),name,classId);
		return scores.size()==0 ? null :scores;
	}

	//查询某班成绩或者全校成绩
	@Override
	public PageResult listPageResult(String classId,Integer currentPage,Integer pageSize) {
	    int beginIndex = (currentPage-1)*pageSize;
		String sql0,sql1;
		List<Score> listData;
		Integer totalCount;
		if (classId.equals("*")||classId.equals("1006")) {
			sql0 = "SELECT * FROM t_score limit  ?,?";
			listData = super.getJdbcTemplate().query(sql0, ScoreRowMapper.getRowMapper(),beginIndex,pageSize);
			sql1 = "SELECT count(0) AS value FROM t_score";
			totalCount = super.getJdbcTemplate().query(sql1, new RowMapper<Integer>() {
                @Override
                public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                    int value = rs.getInt("value");
                    return value;
                }
            }).get(0);
		} else {
			sql0 = "SELECT * FROM t_score WHERE classId=? limit ?,?";
			listData = super.getJdbcTemplate().query(sql0, ScoreRowMapper.getRowMapper(),classId,beginIndex,pageSize);
			sql1 = "SELECT count(0) AS value FROM t_score WHERE classId = ?";
            totalCount = super.getJdbcTemplate().query(sql1, new RowMapper<Integer>() {
                @Override
                public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                    int value = rs.getInt("value");
                    return value;
                }
            },classId).get(0);
		}
		
		PageResult pageResult = new PageResult(listData,totalCount,currentPage,pageSize);
		
		return pageResult;
	}

	//查询成绩最好的学生
	@Override
	public PageResult getBestScore(String classId,Integer currentPage,Integer pageSize) {
	    int beginIndex = (currentPage-1)*pageSize;
	    Integer totalCount = 0;
		String sql0,sql1;
		List<Score> listData;
		if (classId.equals("*")||classId.equals("1006")) {
			sql0 = "SELECT * FROM t_score WHERE (mathScore+chineseScore+englishScore)="
					+ "(select max(mathScore+chineseScore+englishScore) from t_score) limit ?,?";
			listData = super.getJdbcTemplate().query(sql0,new ScoreRowMapper(),beginIndex,pageSize);
			sql1 = "SELECT count(0) AS value FROM t_score WHERE (mathScore+chineseScore+englishScore)="
			    + "(select max(mathScore+chineseScore+englishScore) from t_score)";
			totalCount = super.getJdbcTemplate().query(sql1, new RowMapper<Integer>() {
                @Override
                public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                    int value = rs.getInt("value");
                    return value;
                }
            },classId).get(0);
			
		} else {
			
			sql0 = "SELECT * FROM t_score WHERE (mathScore+chineseScore+englishScore)="
					+ "(select max(mathScore+chineseScore+englishScore) from t_score where classId=?) AND classId=? limit ?,?";
			listData = super.getJdbcTemplate().query(sql0, ScoreRowMapper.getRowMapper(),classId,classId,beginIndex,pageSize);
			sql0 = "SELECT count(0) AS value FROM t_score WHERE (mathScore+chineseScore+englishScore)="
			    + "(select max(mathScore+chineseScore+englishScore) from t_score where classId=?) AND classId=?";
			
		}
		PageResult pageResult = new PageResult(listData,totalCount,currentPage,pageSize);
		return pageResult;
	}

	//查询语文成绩最好的学生
	@Override
	public List<Score> getChineseBestScore(String classId) {
		String sql;
		if (classId.equals("*")||classId.equals("1006")) {
			sql = "SELECT * FROM t_score WHERE englishScore=(select max(englishScore) from t_score)";
			List<Score> scores = super.getJdbcTemplate().query(sql, ScoreRowMapper.getRowMapper());
			return scores;
		} else {
			sql = "SELECT * FROM t_score WHERE chineseScore=(select max(chineseScore) from t_score where classId=?) AND classId=?";
			List<Score> scores = super.getJdbcTemplate().query(sql, new ScoreRowMapper(),classId,classId);
			return scores;
		}
	}

	//查询数学成绩最差的学生
	@Override
	public List<Score> getMathWorstScore(String classId) {
		String sql;
		if (classId.equals("*")||classId.equals("1006")) {
			sql = "SELECT * FROM t_score WHERE mathScore=(select min(mathScore+) from t_score)";
			List<Score> scores = super.getJdbcTemplate().query(sql, new ScoreRowMapper());
			return scores;
		} else {
			sql = "SELECT * FROM t_score WHERE mathScore=(select min(mathScore) from t_score where classId=?) AND classId=?";
			List<Score> scores = super.getJdbcTemplate().query(sql, new ScoreRowMapper(),classId,classId);
			return scores;
		}
	}

	@Override
	public double getEnglishAverageScore(String classId) {
		if (classId.equals("*") || classId.equals("1006")) {
			String sql = "SELECT AVG(englishScore) AS value FROM t_score";
			List<Double> values = super.getJdbcTemplate().query(sql,new RowMapper<Double>() {
				@Override
				public Double mapRow(ResultSet rs, int rowNum) throws SQLException {
					double value = rs.getDouble("value");
					return value;
				}
			});
			return values.size()==1 ? values.get(0) : 0;
		} else {
			String sql = "SELECT AVG(englishScore) AS value FROM t_score WHERE classId=?";
			List<Integer> values = super.getJdbcTemplate().query(sql,new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
					int value = rs.getInt("value");
					return value;
				}
			} , classId);
			return values.size()==1 ? values.get(0) : 0;
		}
	}

	@Override
	public int getEnglishMoreThan90(String classId) {
		if (classId.equals("*") || classId.equals("1006")) {
			String sql = "SELECT count(1) AS value FROM t_score WHERE englishScore>90";
			List<Integer> values = super.getJdbcTemplate().query(sql,new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
					int value = rs.getInt("value");
					return value;
				}
			});
			return values.size()==1 ? values.get(0) : 0;
		} else {
			String sql = "SELECT count(1) AS value FROM t_score WHERE englishScore>90 AND classId=?";
			List<Integer> values = super.getJdbcTemplate().query(sql,new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
					int value = rs.getInt("value");
					return value;
				}
			} , classId);
			return values.size()==1 ? values.get(0) : 0;
		}
	}
}
